package com.samsung.booksmanagement;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.samsung.booksmanagement.Tbl_Database.Tbl_Book;
import com.samsung.booksmanagement.Tbl_Database.Tbl_Schedule;

public class BookDatabase extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "book.db";
	private static final int DATABASE_VERSION = 1;

	public BookDatabase(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(Tbl_Book.CREATE_TBALE);
		db.execSQL(Tbl_Schedule.CREATE_TBALE);
		Log.i("SQL", "Da tao duoc 2 bang");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + Tbl_Schedule.TBL_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + Tbl_Book.TBL_NAME);
		onCreate(db);
	}

	// //////////////////////// Schedule
	// /////////////////////////////////////////////
	public long addScheduleSql(Schedule s) {
		ContentValues values = new ContentValues();
		values.put(Tbl_Schedule.ID_BOOK, s.getBookId());
		values.put(Tbl_Schedule.TIME, s.getTime());
		values.put(Tbl_Schedule.SCHEDULE, s.getSchedule());
		SQLiteDatabase sqlitedb = this.getReadableDatabase();
		return sqlitedb.insert(Tbl_Schedule.TBL_NAME, null, values);
	}

	public int deleteScheduleSql(int key) {
		SQLiteDatabase sqlitedb = this.getWritableDatabase();
		return sqlitedb.delete(Tbl_Schedule.TBL_NAME, Tbl_Schedule.ID + "="
				+ key, null);
	}

	public int getIdScheduleByBookIdSql(int bookId) {
		SQLiteDatabase sqlitedb = this.getReadableDatabase();
		Cursor cursor = sqlitedb.rawQuery("SELECT * FROM "
				+ Tbl_Schedule.TBL_NAME + " WHERE " + Tbl_Schedule.ID_BOOK
				+ " like '" + bookId + "'", null);
		if (cursor.moveToFirst()) {

			return cursor.getInt(cursor.getColumnIndex(Tbl_Schedule.ID));
		} else
			return -1;
	}

	// //////////////////////// Book
	// /////////////////////////////////////////////
	public ArrayList<Book> listAllBookSql() {

		ArrayList<Book> bookStore = new ArrayList<Book>();
		SQLiteDatabase sqlitedb = this.getReadableDatabase();

		Cursor cursor = sqlitedb.query(Tbl_Book.TBL_NAME, null, null, null,
				null, null, null);
		while (cursor.moveToNext()) {
			Book book = new Book();
			book.setId(cursor.getInt(cursor.getColumnIndex(Tbl_Book.ID)));// 1
			book.setName(cursor.getString(cursor.getColumnIndex(Tbl_Book.NAME)));// 2
			book.setAuthor(cursor.getString(cursor
					.getColumnIndex(Tbl_Book.AUTHOR)));// 3
			book.setPublisher(cursor.getString(cursor
					.getColumnIndex(Tbl_Book.PUBLISHER)));// 4
			book.setPublishDate(cursor.getString(cursor
					.getColumnIndex(Tbl_Book.PUBLISH_DATE)));// 5
			book.setImage(cursor.getString(cursor
					.getColumnIndex(Tbl_Book.IMAGE)));// 6

			bookStore.add(book);
		}
		cursor.close();

		return bookStore;
	}

	public long addBookSql(Book book) {
		ContentValues values = new ContentValues();
		values.put(Tbl_Book.NAME, book.getName());
		values.put(Tbl_Book.AUTHOR, book.getAuthor());
		values.put(Tbl_Book.PUBLISHER, book.getAuthor());
		values.put(Tbl_Book.PUBLISH_DATE, book.getPublishDate());
		values.put(Tbl_Book.IMAGE, book.getImage());
		SQLiteDatabase sqlitedb = this.getReadableDatabase();
		return sqlitedb.insert(Tbl_Book.TBL_NAME, null, values);
	}

	public int deleteBookSql(int key) {
		SQLiteDatabase sqlitedb = this.getWritableDatabase();
		deleteScheduleSql(key);
		return sqlitedb
				.delete(Tbl_Book.TBL_NAME, Tbl_Book.ID + "=" + key, null);
	}

	public int updateBookSql(int key, Book book) {
		ContentValues values = new ContentValues();
		values.put(Tbl_Book.NAME, book.getName());
		values.put(Tbl_Book.AUTHOR, book.getAuthor());
		values.put(Tbl_Book.PUBLISHER, book.getAuthor());
		values.put(Tbl_Book.PUBLISH_DATE, book.getPublishDate());
		values.put(Tbl_Book.IMAGE, book.getImage());
		SQLiteDatabase sqlitedb = this.getWritableDatabase();
		return sqlitedb.update(Tbl_Book.TBL_NAME, values, Tbl_Book.ID + "="
				+ key, null);
	}

	public ArrayList<Book> searchBookSql(String keyword) {
		ArrayList<Book> bookStore = new ArrayList<Book>();
		SQLiteDatabase sqlitedb = this.getReadableDatabase();
		Cursor cursor = sqlitedb.rawQuery("SELECT * FROM " + Tbl_Book.TBL_NAME
				+ " WHERE " + Tbl_Book.NAME + " like '" + keyword + "'", null);
		while (cursor.moveToNext()) {
			Book book = new Book();
			book.setId(cursor.getInt(cursor.getColumnIndex(Tbl_Book.ID)));// 1
			book.setName(cursor.getString(cursor.getColumnIndex(Tbl_Book.NAME)));// 2
			book.setAuthor(cursor.getString(cursor
					.getColumnIndex(Tbl_Book.AUTHOR)));// 3
			book.setPublisher(cursor.getString(cursor
					.getColumnIndex(Tbl_Book.PUBLISHER)));// 4
			book.setPublishDate(cursor.getString(cursor
					.getColumnIndex(Tbl_Book.PUBLISH_DATE)));// 5
			book.setImage(cursor.getString(cursor
					.getColumnIndex(Tbl_Book.IMAGE)));// 6

			bookStore.add(book);
		}
		cursor.close();

		return bookStore;
	}

	public Book viewBookSql(int _id) {
		Book bookStore = new Book();
		SQLiteDatabase sqlitedb = this.getReadableDatabase();
		Cursor cursor = sqlitedb.rawQuery("SELECT * FROM " + Tbl_Book.TBL_NAME
				+ " WHERE " + Tbl_Book.ID + " like '" + _id + "'", null);
		if (cursor.moveToFirst()) {
			bookStore = new Book();
			bookStore.setId(cursor.getInt(cursor.getColumnIndex(Tbl_Book.ID)));// 1
			bookStore.setName(cursor.getString(cursor
					.getColumnIndex(Tbl_Book.NAME)));// 2
			bookStore.setAuthor(cursor.getString(cursor
					.getColumnIndex(Tbl_Book.AUTHOR)));// 3
			bookStore.setPublisher(cursor.getString(cursor
					.getColumnIndex(Tbl_Book.PUBLISHER)));// 4
			bookStore.setPublishDate(cursor.getString(cursor
					.getColumnIndex(Tbl_Book.PUBLISH_DATE)));// 5
			bookStore.setImage(cursor.getString(cursor
					.getColumnIndex(Tbl_Book.IMAGE)));// 6
		}
		cursor.close();
		return bookStore;
	}
}
